package com.lawyer.repository;

import java.util.List;

import javax.transaction.Transactional;

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.rest.core.annotation.RepositoryRestResource;

import com.lawyer.entity.Lawyer;

@RepositoryRestResource(exported = false)
public interface LawyerRepository extends PagingAndSortingRepository<Lawyer, String>, JpaSpecificationExecutor<Lawyer> {

	@Query(value ="select * from lawyer where concat(nick_name,case_type,introduce_myself) like %?1% and certification = 2 \n#pageable\n",
			countQuery = "select count(*) from lawyer where concat(nick_name,case_type,introduce_myself) like %?1% and certification = 2",  nativeQuery = true)
	Page<Lawyer> queryByKeyWord(String keyWord,Pageable pageable);
	
	
	List<Lawyer> findByRecommendAndOnLineAndCertificationOrderByStarLevelDesc(Boolean isRecommend,String olLine,Integer certification);
	
	Lawyer findByUsernameAndPassword(String username,String password);
	
	Lawyer findByUsername(String username);
	
	
	Lawyer findByIdAndOnLineAndIsFree(String lawyerId,String onLine,boolean isFree);
	
	Lawyer findByIdAndOnLine(String lawyerId,String onLine);
	
	  @Transactional
	  @Modifying(clearAutomatically = true)
	  @Query(value = "update lawyer set cid = ?1 where id  = ?2 ", nativeQuery = true)
	    void updateCid(String cid, String id);
	  
	  
		/**
		 * 当天新注册用户
		 */
		@Query(value = "select count(id) from Lawyer where to_days(createdDate) = to_days(now())")
		Object registerCurrDay();
		/**
		 * 最近7天用户注册量
		 */
		@Query(value = "select count(id) from lawyer where date_sub(curdate(), interval 7 day) <= date(created_date)", nativeQuery = true)
		Object register7Day();
		/**
		 * 最近30天用户注册量
		 */
		@Query(value = "select count(id) from lawyer where date_sub(curdate(), interval 30 day) <= date(created_date)", nativeQuery = true)
		Object register30Day();
		
		/**
		 * 总用户量
		 */
		@Query(value = "select count(id) from lawyer", nativeQuery = true)
		Object totalLawyerCount();

	  
}
